package project.dao;

import db.DbConn;
import project.entity.Project;
import user.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ProjectDao {
    
    /** 
     * 查询某用户是否参加项目
     * @author ZengQi
     * @date 2021/11/26 0026 14:40
     * @param userId
     * @return boolean
     */
    public int ifJoin(int userId) {
        int ifJoin = 0;
        String sql = "SELECT COUNT(*) FROM t_project_user WHERE user_id="+userId;
        System.out.println("构造的sql语句是："+sql);
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ifJoin=1;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return ifJoin;
    }

    /**
     * 项目成员查看项目列表功能，分页
     * 查看所有已审核的project
     * 模糊查询
     * @return
     */
    public List<Project> memProjectList(String projectName, int page, int limit, int userId) {
        List<Project> projectList = new ArrayList<>();
        String sql = "SELECT * FROM t_project WHERE state=2";
        if(projectName != null && (projectName.length()!=0)) {
            sql += " AND projectName like '%" + projectName + "%'";
        }
        int start = (page-1)*limit;
        int end = limit;
        sql = sql + " limit " + start + "," + end;
        System.out.println("构造的sql语句是："+sql);

        SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Project project = new Project();
                int id = rs.getInt("id");
                String leader = rs.getString("leader");
                String projectname = rs.getString("projectName");
                String detail = rs.getString("detail");
                int state = rs.getInt("state");
                int ifJoin = ifJoin(userId);
                project.setId(id);
                project.setLeader(leader);
                project.setProjectName(projectname);
                project.setCreateTime(ft.format(rs.getTimestamp("createTime")));
                project.setIfJoin(ifJoin);
                project.setDetail(detail);
                project.setState(state);
                projectList.add(project);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        System.out.println(projectList);
        return projectList;
    }

    /**
     * 查询已审核项目的数量
     * @return
     */
    public int queryMemAllProjects() {
        String sql = "SELECT count(*) as total FROM t_project WHERE state=2";
        System.out.println("构造的sql语句是："+sql);
        int totalCount = 0;
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                totalCount = rs.getInt("total");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return totalCount;
    }

    /**
     * 项目主管得到所有项目列表，分页
     * @param projectName
     * @param page
     * @param limit
     * @return
     */
    public List<Project> direProjectList(String projectName, int page, int limit) {
        List<Project> projectList = new ArrayList<>();
        String sql = "SELECT * FROM t_project";
        if(projectName != null && (projectName.length()!=0)) {
            sql += " WHERE projectName like '%" + projectName + "%'";
        }
        int start = (page-1)*limit;
        int end = limit;
        sql = sql + " limit " + start + "," + end;
        System.out.println("构造的sql语句是："+sql);
        SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Project project = new Project();
                int id = rs.getInt("id");
                String leader = rs.getString("leader");
                String projectname = rs.getString("projectName");
                String detail = rs.getString("detail");
                int state = rs.getInt("state");
                project.setId(id);
                project.setLeader(leader);
                project.setProjectName(projectname);
                project.setCreateTime(ft.format(rs.getTimestamp(4)));
                project.setDetail(detail);
                project.setState(state);
                projectList.add(project);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return projectList;
    }

    /**
     * 查询所有项目的数量
     * @return
     */
    public int queryDireAllProjects() {
        String sql = "SELECT count(*) as total FROM t_project";
        System.out.println("构造的sql语句是："+sql);
        int totalCount = 0;
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                totalCount = rs.getInt("total");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return totalCount;
    }

    /**
     * 项目经理查看自己管理的项目列表，分页
     * 多表查询
     * 通过中间表t_project_user
     * @param projectName
     * @param page
     * @param limit
     * @return
     */
    public List<Project> magProjectList(String projectName, int page, int limit, int userId) {
        List<Project> projectList = new ArrayList<>();
        String sql1 = "SELECT t_project.* FROM t_project, t_user, t_project_user WHERE t_project.id=t_project_user.project_id AND t_user.id=t_project_user.user_id AND t_user.id="+userId;
        if(projectName != null && (projectName.length()!=0)) {
            sql1 += " AND projectName like '%" + projectName + "%'";
        }
        int start = (page-1)*limit;
        int end = limit;
        sql1 = sql1 + " limit " + start + "," + end;
        System.out.println("构造的sql语句是："+sql1);
        SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Project project = new Project();
                int id = rs.getInt("id");
                System.out.println(id);

                String sql2="select t_user.true_name from t_user,t_project_user,t_project where t_project.id=t_project_user.project_id AND t_user.id=t_project_user.user_id AND t_user.role_id=3 AND t_project_user.project_id="+id;
                PreparedStatement ps2 = connection.prepareStatement(sql2);
                ResultSet rs2 = ps2.executeQuery();
//                String memlist="";
                List<String> memlist=new ArrayList<>();
                while(rs2.next())
                {
                    memlist.add(rs2.getString("true_name"));
                }

                String leader = rs.getString("leader");
                String projectname = rs.getString("projectName");
//                String memberList = rs.getString(5);

                String detail = rs.getString("detail");
                int state = rs.getInt("state");
                project.setId(id);
                project.setLeader(leader);
                project.setProjectName(projectname);
                project.setCreateTime(ft.format(rs.getTimestamp("createTime")));
                project.setMemberList(memlist);
                project.setDetail(detail);
                project.setState(state);
                projectList.add(project);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return projectList;
    }

    /**
     * 查询经理管理的所有项目数量
     * @param userId
     * @return
     */
    public int queryMagAllProjects(int userId) {
        String sql = "SELECT COUNT(*) as total FROM t_project, t_user, t_project_user WHERE t_project.id=t_project_user.project_id AND t_user.id=t_project_user.user_id AND t_user.id="+userId;
        System.out.println("构造的sql语句是："+sql);
        int totalCount = 0;
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                totalCount = rs.getInt("total");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return totalCount;
    }

    /**
     * 查询某个用户所参与的项目
     * @param projectName
     * @param page
     * @param limit
     * @param userId
     * @return
     */
    public List<Project> queryUserProjectList(int page, int limit, String projectName, int userId)
    {
        List<Project> projectList = new ArrayList<>();
        String sql = "SELECT t_project.* FROM t_project, t_project_user WHERE t_project.id=t_project_user.project_id AND t_project_user.user_id="+userId;
        if(projectName != null && (projectName.length()!=0)) {
            sql += " AND projectName like '%" + projectName + "%'";
        }
        int start = (page-1)*limit;
        int end = limit;
        sql = sql + " limit " + start + "," + end;
        System.out.println("构造的sql语句是："+sql);
        System.out.println("userId："+userId);
        SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Project project = new Project();
                int id = rs.getInt("id");

                String leader = rs.getString("leader");
                String projectname = rs.getString("projectName");
                String detail = rs.getString("detail");
                int state = rs.getInt("state");
                String sql2="select t_user.true_name from t_user,t_project_user,t_project where t_project.id=t_project_user.project_id AND t_user.id=t_project_user.user_id AND t_user.role_id=3 AND t_project_user.project_id="+id;
                PreparedStatement ps2 = connection.prepareStatement(sql2);
                ResultSet rs2 = ps2.executeQuery();
                List<String> memlist=new ArrayList<>();
                while(rs2.next())
                {
                    memlist.add(rs2.getString("true_name"));
                }
                project.setId(id);
                project.setLeader(leader);
                project.setProjectName(projectname);
                project.setCreateTime(ft.format(rs.getTimestamp("createTime")));
                project.setDetail(detail);
                project.setState(state);
                project.setMemberList(memlist);
                projectList.add(project);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return projectList;

    }


    /**
     * 查询某项目的成员列表
     * @return
     */
    private List<String> queryMemberList() {
        List<String> memberList = new ArrayList<>();

        return memberList;
    }

    public boolean addproject_User(int project_id,int user_id) {
        String sql = "INSERT INTO t_project_user(project_id,user_id) VALUES(?,?)";
        boolean bool = false;
        Connection connection = DbConn.getconn("db_project");
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1,project_id);
            ps.setInt(2,user_id);
            System.out.println("userId："+user_id);
            System.out.println("project_id："+project_id);
            int rs = ps.executeUpdate();
            if(rs > 0) {
                bool = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return bool;
    }

    //添加项目
    public boolean addProject(Project project,int userId) {
        String sql1 = "INSERT INTO t_project(id,leader,projectName,createTime,detail,state) VALUES(?,?,?,?,?,?)";
        boolean bool = false;
        Connection connection = DbConn.getconn("db_project");
        try {
            PreparedStatement ps = connection.prepareStatement(sql1);
            ps.setInt(1,project.getId());
            ps.setString(2, project.getLeader());
            ps.setString(3,project.getProjectName());
            ps.setString(4,project.getCreateTime());
            ps.setString(5,project.getDetail());
            ps.setInt(6,0);//默认新加入的项目为未审核状态

            int rs = ps.executeUpdate();
//            if(rs > 0) {
//                bool = true;
//            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
//
//        String sql2= "select max(id) from t_project";
//        try {
//            PreparedStatement ps = connection.prepareStatement(sql2);
//            ResultSet rs = ps.executeQuery();
//            int max_id=rs.getInt(0);
//            String sql3 = "INSERT INTO t_project_user(project_id,user_id) VALUES(?,?)";
//            PreparedStatement ps2 = connection.prepareStatement(sql3);
//            ps2.setInt(1, max_id);
//            ps2.setInt(2,userId);
//
//            int rs2 = ps2.executeUpdate();
//            if(rs2 > 0) {
//                bool = true;
//            }
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }

        String sql2 = "INSERT INTO t_project_user(project_id,user_id) VALUES(?,?)";
        try {
            PreparedStatement ps = connection.prepareStatement(sql2);
            ps.setInt(1, project.getId());
            ps.setInt(2,userId);

            int rs = ps.executeUpdate();
            if(rs > 0) {
                bool = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
       return bool;
    }


    public Project findProjectById(Integer id) {
        String sql = "SELECT * FROM t_project WHERE id=?";
        Project project = null;
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                project = new Project();
                project.setId(rs.getInt("id"));
                project.setProjectName(rs.getString("projectName"));
                project.setLeader(rs.getString("leader"));
                project.setDetail(rs.getString("detail"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return project;
    }

    public void updateProject(Project project) {
        String sql = "UPDATE t_project SET leader=?,projectName=?,detail=?"+ "WHERE id=?";
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1,project.getLeader());
            ps.setString(2,project.getProjectName());
            ps.setString(3,project.getDetail());

            System.out.println("===================");
            String id = String.valueOf(project.getId());
            ps.setString(4,id);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void deleteProject(int id) {
        String sql = "DELETE FROM t_project WHERE id=?";
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        String sql2 = "DELETE FROM t_project_user WHERE project_id=?";
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql2);
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    public void acceptProject(int id) {
        String sql = "UPDATE t_project SET t_project.state=2 WHERE id=?";
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            System.out.println(id);
//            String id = String.valueOf(project.getId());
            ps.setInt(1,id);
//            ps.setString(4,id);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void rejectProject(int id) {
        String sql = "UPDATE t_project SET t_project.state=1 WHERE id=?";
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            System.out.println(id);
//            String id = String.valueOf(project.getId());
            ps.setInt(1,id);
//            ps.setString(4,id);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 通过项目id查询项目名称
     * @param id
     * @return
     */
    public String findProjectNameById(int id) {
        String sql = "SELECT projectName FROM t_project WHERE id="+id;
        System.out.println("构造的sql语句是："+sql);
        String projectName = null;
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                projectName = rs.getString("projectName");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return projectName;
    }
}
